package com.csz.dao.impl;

import com.csz.bean.Student;
import com.csz.dao.GradeDao;
import com.csz.dao.StudentDao;
import com.csz.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public int total(String name, String stuno, int sex) {
        int total = 0;
        List par = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" SELECT count(*) from student  where 1=1 ");
            if (name != null && name.length() > 0) {
                sql.append(" and stuname like ? ");
                par.add("%" + name + "%");
            }
            if (stuno != null && stuno.length() > 0) {
                sql.append(" and stuno=? ");
                par.add(stuno);
            }
            if (sex != -1) {
                sql.append(" and sex=? ");
                par.add(sex);
            }
            resultSet = query(sql.toString(), par);
            while (resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public List<Student> getStudents(String name ,String stuno,int sex,int pageIndex,int pageSize) {
        // 数据库相关操作
        List list = new ArrayList<Student>();
        List par = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" SELECT * from student  where 1=1 ");
            if (name!=null&&name.length()>0){
                sql.append(" and stuname like ? ");
                par.add("%"+name+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                sql.append(" and stuno=? ");
                par.add(stuno);
            }
            if (sex!=-1){
                sql.append(" and sex=? ");
                par.add(sex);
            }
            //分页条件
            sql.append(" limit ?,? ");  //  0,5 代表小标为1，取5条
            // 第一页 0，5  第二页 5，5 第三页10，5  第n页 (n-1)*5
            par.add((pageIndex-1)*pageSize);
            par.add(pageSize);

            resultSet = query(sql.toString(), par);
            while (resultSet.next()){
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));

                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));

                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                list.add(student);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
}
